// Licensed to the Apache Software Foundation (ASF) under one
// or more contributor license agreements.  See the NOTICE file
// distributed with this work for additional information
// regarding copyright ownership.  The ASF licenses this file
// to you under the Apache License, Version 2.0 (the
// "License"); you may not use this file except in compliance
// with the License.  You may obtain a copy of the License at
//
//   http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing,
// software distributed under the License is distributed on an
// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
// KIND, either express or implied.  See the License for the
// specific language governing permissions and limitations
// under the License.

package org.apache.doris.nereids.sqltest;

import org.apache.doris.catalog.Env;
import org.apache.doris.catalog.MetaIdGenerator.IdGeneratorBuffer;
import org.apache.doris.nereids.CascadesContext;
import org.apache.doris.nereids.rules.exploration.mv.LogicalCompatibilityContext;
import org.apache.doris.nereids.rules.exploration.mv.MaterializedViewUtils;
import org.apache.doris.nereids.rules.exploration.mv.StructInfo;
import org.apache.doris.nereids.rules.exploration.mv.mapping.RelationMapping;
import org.apache.doris.nereids.rules.exploration.mv.mapping.SlotMapping;
import org.apache.doris.nereids.trees.expressions.StatementScopeIdGenerator;
import org.apache.doris.nereids.trees.plans.Plan;
import org.apache.doris.nereids.util.MemoPatternMatchSupported;
import org.apache.doris.utframe.TestWithFeService;

import java.util.BitSet;

public abstract class SqlTestBase extends TestWithFeService implements MemoPatternMatchSupported {
    @Override
    protected void runBeforeAll() throws Exception {
        createDatabase("test");
        connectContext.setDatabase("test");

        // make table id is larger than Integer.MAX_VALUE
        IdGeneratorBuffer idGeneratorBuffer =
                Env.getCurrentEnv().getIdGeneratorBuffer(Integer.MAX_VALUE + 10L);
        idGeneratorBuffer.getNextId();

        createTables(
                "CREATE TABLE IF NOT EXISTS T0 (\n"
                        + "    id bigint,\n"
                        + "    score bigint\n"
                        + ")\n"
                        + "DUPLICATE KEY(id)\n"
                        + "DISTRIBUTED BY HASH(id, score) BUCKETS 10\n"
                        + "PROPERTIES (\n"
                        + "  \"replication_num\" = \"1\", \n"
                        + "  \"colocate_with\" = \"T0\"\n"
                        + ")\n",
                "CREATE TABLE IF NOT EXISTS T1 (\n"
                        + "    id bigint,\n"
                        + "    score bigint\n"
                        + ")\n"
                        + "DUPLICATE KEY(id)\n"
                        + "DISTRIBUTED BY HASH(id, score) BUCKETS 10\n"
                        + "PROPERTIES (\n"
                        + "  \"replication_num\" = \"1\", \n"
                        + "  \"colocate_with\" = \"T0\"\n"
                        + ")\n",
                "CREATE TABLE IF NOT EXISTS T2 (\n"
                        + "    id bigint,\n"
                        + "    score bigint\n"
                        + ")\n"
                        + "DUPLICATE KEY(id)\n"
                        + "DISTRIBUTED BY HASH(id) BUCKETS 10\n"
                        + "PROPERTIES (\n"
                        + "  \"replication_num\" = \"1\"\n"
                        + ")\n",
                "CREATE TABLE IF NOT EXISTS T3 (\n"
                        + "    id bigint,\n"
                        + "    score bigint\n"
                        + ")\n"
                        + "DUPLICATE KEY(id)\n"
                        + "DISTRIBUTED BY HASH(id) BUCKETS 1\n"
                        + "PROPERTIES (\n"
                        + "  \"replication_num\" = \"1\"\n"
                        + ")\n",
                "CREATE TABLE IF NOT EXISTS T4 (\n"
                        + "    id bigint,\n"
                        + "    score bigint\n"
                        + ")\n"
                        + "DUPLICATE KEY(id)\n"
                        + "AUTO PARTITION BY LIST(`id`)\n"
                        + "(\n"
                        + ")\n"
                        + "DISTRIBUTED BY HASH(id) BUCKETS 1\n"
                        + "PROPERTIES (\n"
                        + "  \"replication_num\" = \"1\"\n"
                        + ")\n",
                "CREATE TABLE IF NOT EXISTS orders  (\n"
                        + "      o_orderkey       INTEGER NOT NULL,\n"
                        + "      o_custkey        INTEGER NOT NULL,\n"
                        + "      o_orderstatus    CHAR(1) NOT NULL,\n"
                        + "      o_totalprice     DECIMALV3(15,2) NOT NULL,\n"
                        + "      o_orderdate      DATE NOT NULL,\n"
                        + "      o_orderpriority  CHAR(15) NOT NULL,  \n"
                        + "      o_clerk          CHAR(15) NOT NULL, \n"
                        + "      o_shippriority   INTEGER NOT NULL,\n"
                        + "      O_COMMENT        VARCHAR(79) NOT NULL\n"
                        + "    )\n"
                        + "    DUPLICATE KEY(o_orderkey, o_custkey)\n"
                        + "    DISTRIBUTED BY HASH(o_orderkey) BUCKETS 3\n"
                        + "    PROPERTIES (\n"
                        + "      \"replication_num\" = \"1\"\n"
                        + "    );",
                "CREATE TABLE IF NOT EXISTS lineitem (\n"
                        + "      l_orderkey    INTEGER NOT NULL,\n"
                        + "      l_partkey     INTEGER NOT NULL,\n"
                        + "      l_suppkey     INTEGER NOT NULL,\n"
                        + "      l_linenumber  INTEGER NOT NULL,\n"
                        + "      l_quantity    DECIMALV3(15,2) NOT NULL,\n"
                        + "      l_extendedprice  DECIMALV3(15,2) NOT NULL,\n"
                        + "      l_discount    DECIMALV3(15,2) NOT NULL,\n"
                        + "      l_tax         DECIMALV3(15,2) NOT NULL,\n"
                        + "      l_returnflag  CHAR(1) NOT NULL,\n"
                        + "      l_linestatus  CHAR(1) NOT NULL,\n"
                        + "      l_shipdate    DATE NOT NULL,\n"
                        + "      l_commitdate  DATE NOT NULL,\n"
                        + "      l_receiptdate DATE NOT NULL,\n"
                        + "      l_shipinstruct CHAR(25) NOT NULL,\n"
                        + "      l_shipmode     CHAR(10) NOT NULL,\n"
                        + "      l_comment      VARCHAR(44) NOT NULL\n"
                        + "    )\n"
                        + "    DUPLICATE KEY(l_orderkey, l_partkey, l_suppkey, l_linenumber)\n"
                        + "    DISTRIBUTED BY HASH(l_orderkey) BUCKETS 3\n"
                        + "    PROPERTIES (\n"
                        + "      \"replication_num\" = \"1\"\n"
                        + "    );",
                "CREATE TABLE IF NOT EXISTS partsupp (\n"
                        + "      ps_partkey     INTEGER NOT NULL,\n"
                        + "      ps_suppkey     INTEGER NOT NULL,\n"
                        + "      ps_availqty    INTEGER NOT NULL,\n"
                        + "      ps_supplycost  DECIMALV3(15,2)  NOT NULL,\n"
                        + "      ps_comment     VARCHAR(199) NOT NULL \n"
                        + "    )\n"
                        + "    DUPLICATE KEY(ps_partkey, ps_suppkey)\n"
                        + "    DISTRIBUTED BY HASH(ps_partkey) BUCKETS 3\n"
                        + "    PROPERTIES (\n"
                        + "      \"replication_num\" = \"1\"\n"
                        + "    );",
                "CREATE TABLE IF NOT EXISTS part (\n"
                        + "  P_PARTKEY     INTEGER NOT NULL,\n"
                        + "  P_NAME        VARCHAR(55) NOT NULL,\n"
                        + "  P_MFGR        CHAR(25) NOT NULL,\n"
                        + "  P_BRAND       CHAR(10) NOT NULL,\n"
                        + "  P_TYPE        VARCHAR(25) NOT NULL,\n"
                        + "  P_SIZE        INTEGER NOT NULL,\n"
                        + "  P_CONTAINER   CHAR(10) NOT NULL,\n"
                        + "  P_RETAILPRICE DECIMAL(15,2) NOT NULL,\n"
                        + "  P_COMMENT     VARCHAR(23) NOT NULL \n"
                        + ")\n"
                        + "DUPLICATE KEY(P_PARTKEY, P_NAME)\n"
                        + "DISTRIBUTED BY HASH(P_PARTKEY) BUCKETS 3\n"
                        + "PROPERTIES (\n"
                        + "  \"replication_num\" = \"1\"\n"
                        + ")",
                "CREATE TABLE customer (\n"
                        + "        c_custkey     int NOT NULL,\n"
                        + "        c_name        VARCHAR(25) NOT NULL,\n"
                        + "        c_address     VARCHAR(40) NOT NULL,\n"
                        + "        c_nationkey   int NOT NULL,\n"
                        + "        c_phone       VARCHAR(15) NOT NULL,\n"
                        + "        c_acctbal     decimal(15, 2)   NOT NULL,\n"
                        + "        c_mktsegment  VARCHAR(10) NOT NULL,\n"
                        + "        c_comment     VARCHAR(117) NOT NULL\n"
                        + "    )ENGINE=OLAP\n"
                        + "    DUPLICATE KEY(`c_custkey`)\n"
                        + "    COMMENT \"OLAP\"\n"
                        + "    DISTRIBUTED BY HASH(`c_custkey`) BUCKETS 24\n"
                        + "    PROPERTIES (\n"
                        + "        \"replication_num\" = \"1\"\n"
                        + "    );",
                "CREATE TABLE supplier (\n"
                        + "        s_suppkey       int NOT NULL,\n"
                        + "        s_name        VARCHAR(25) NOT NULL,\n"
                        + "        s_address     VARCHAR(40) NOT NULL,\n"
                        + "        s_nationkey   int NOT NULL,\n"
                        + "        s_phone       VARCHAR(15) NOT NULL,\n"
                        + "        s_acctbal     decimal(15, 2) NOT NULL,\n"
                        + "        s_comment     VARCHAR(101) NOT NULL\n"
                        + "    )ENGINE=OLAP\n"
                        + "    DUPLICATE KEY(`s_suppkey`)\n"
                        + "    COMMENT \"OLAP\"\n"
                        + "    DISTRIBUTED BY HASH(`s_suppkey`) BUCKETS 12\n"
                        + "    PROPERTIES (\n"
                        + "        \"replication_num\" = \"1\"\n"
                        + "    );",
                "CREATE TABLE `nation` (\n"
                        + "    `n_nationkey` int(11) NOT NULL,\n"
                        + "    `n_name`      varchar(25) NOT NULL,\n"
                        + "    `n_regionkey` int(11) NOT NULL,\n"
                        + "    `n_comment`   varchar(152) NULL\n"
                        + "    ) ENGINE=OLAP\n"
                        + "    DUPLICATE KEY(`N_NATIONKEY`)\n"
                        + "    COMMENT \"OLAP\"\n"
                        + "    DISTRIBUTED BY HASH(`N_NATIONKEY`) BUCKETS 1\n"
                        + "    PROPERTIES (\n"
                        + "        \"replication_num\" = \"1\"\n"
                        + "    );",
                "CREATE TABLE region  (\n"
                        + "        r_regionkey      int NOT NULL,\n"
                        + "        r_name       VARCHAR(25) NOT NULL,\n"
                        + "        r_comment    VARCHAR(152)\n"
                        + "    )ENGINE=OLAP\n"
                        + "    DUPLICATE KEY(`r_regionkey`)\n"
                        + "    COMMENT \"OLAP\"\n"
                        + "    DISTRIBUTED BY HASH(`r_regionkey`) BUCKETS 1\n"
                        + "    PROPERTIES (\n"
                        + "        \"replication_num\" = \"1\"\n"
                        + "    );",
                "CREATE TABLE IF NOT EXISTS call_center (\n"
                        + "    cc_call_center_sk bigint,\n"
                        + "    cc_call_center_id char(16),\n"
                        + "    cc_rec_start_date date,\n"
                        + "    cc_rec_end_date date,\n"
                        + "    cc_closed_date_sk integer,\n"
                        + "    cc_open_date_sk integer,\n"
                        + "    cc_name varchar(50),\n"
                        + "    cc_class varchar(50),\n"
                        + "    cc_employees integer,\n"
                        + "    cc_sq_ft integer,\n"
                        + "    cc_hours char(20),\n"
                        + "    cc_manager varchar(40),\n"
                        + "    cc_mkt_id integer,\n"
                        + "    cc_mkt_class char(50),\n"
                        + "    cc_mkt_desc varchar(100),\n"
                        + "    cc_market_manager varchar(40),\n"
                        + "    cc_division integer,\n"
                        + "    cc_division_name varchar(50),\n"
                        + "    cc_company integer,\n"
                        + "    cc_company_name char(50),\n"
                        + "    cc_street_number char(10),\n"
                        + "    cc_street_name varchar(60),\n"
                        + "    cc_street_type char(15),\n"
                        + "    cc_suite_number char(10),\n"
                        + "    cc_city varchar(60),\n"
                        + "    cc_county varchar(30),\n"
                        + "    cc_state char(2),\n"
                        + "    cc_zip char(10),\n"
                        + "    cc_country varchar(20),\n"
                        + "    cc_gmt_offset decimal(5,2),\n"
                        + "    cc_tax_percentage decimal(5,2)\n"
                        + " )\n"
                        + "DUPLICATE KEY(cc_call_center_sk, cc_call_center_id)\n"
                        + "DISTRIBUTED BY HASH(cc_call_center_sk) BUCKETS 3\n"
                        + "PROPERTIES (\n"
                        + "  \"replication_num\" = \"1\"\n"
                        + ")\n",
                "CREATE TABLE IF NOT EXISTS catalog_page (\n"
                        + "    cp_catalog_page_sk bigint,\n"
                        + "    cp_catalog_page_id char(16),\n"
                        + "    cp_start_date_sk integer,\n"
                        + "    cp_end_date_sk integer,\n"
                        + "    cp_department varchar(50),\n"
                        + "    cp_catalog_number integer,\n"
                        + "    cp_catalog_page_number integer,\n"
                        + "    cp_description varchar(100),\n"
                        + "    cp_type varchar(100)\n"
                        + ")\n"
                        + "DUPLICATE KEY(cp_catalog_page_sk, cp_catalog_page_id)\n"
                        + "DISTRIBUTED BY HASH(cp_catalog_page_sk) BUCKETS 3\n"
                        + "PROPERTIES (\n"
                        + "  \"replication_num\" = \"1\"\n"
                        + ")\n"
                        + "\n",
                "CREATE TABLE IF NOT EXISTS catalog_returns (\n"
                        + "    cr_returned_date_sk bigint,\n"
                        + "    cr_returned_time_sk bigint,\n"
                        + "    cr_item_sk bigint,\n"
                        + "    cr_refunded_customer_sk bigint,\n"
                        + "    cr_refunded_cdemo_sk bigint,\n"
                        + "    cr_refunded_hdemo_sk bigint,\n"
                        + "    cr_refunded_addr_sk bigint,\n"
                        + "    cr_returning_customer_sk bigint,\n"
                        + "    cr_returning_cdemo_sk bigint,\n"
                        + "    cr_returning_hdemo_sk bigint,\n"
                        + "    cr_returning_addr_sk bigint,\n"
                        + "    cr_call_center_sk bigint,\n"
                        + "    cr_catalog_page_sk bigint,\n"
                        + "    cr_ship_mode_sk bigint,\n"
                        + "    cr_warehouse_sk bigint,\n"
                        + "    cr_reason_sk bigint,\n"
                        + "    cr_order_number bigint,\n"
                        + "    cr_return_quantity integer,\n"
                        + "    cr_return_amount decimal(7,2),\n"
                        + "    cr_return_tax decimal(7,2),\n"
                        + "    cr_return_amt_inc_tax decimal(7,2),\n"
                        + "    cr_fee decimal(7,2),\n"
                        + "    cr_return_ship_cost decimal(7,2),\n"
                        + "    cr_refunded_cash decimal(7,2),\n"
                        + "    cr_reversed_charge decimal(7,2),\n"
                        + "    cr_store_credit decimal(7,2),\n"
                        + "    cr_net_loss decimal(7,2)\n"
                        + ")\n"
                        + "DUPLICATE KEY(cr_returned_date_sk, cr_returned_time_sk, cr_item_sk, cr_refunded_customer_sk)\n"
                        + "DISTRIBUTED BY HASH(cr_refunded_customer_sk) BUCKETS 3\n"
                        + "PROPERTIES (\n"
                        + "  \"replication_num\" = \"1\"\n"
                        + ")\n",
                "CREATE TABLE IF NOT EXISTS catalog_sales (\n"
                        + "    cs_sold_date_sk bigint,\n"
                        + "    cs_sold_time_sk bigint,\n"
                        + "    cs_ship_date_sk bigint,\n"
                        + "    cs_bill_customer_sk bigint,\n"
                        + "    cs_bill_cdemo_sk bigint,\n"
                        + "    cs_bill_hdemo_sk bigint,\n"
                        + "    cs_bill_addr_sk bigint,\n"
                        + "    cs_ship_customer_sk bigint,\n"
                        + "    cs_ship_cdemo_sk bigint,\n"
                        + "    cs_ship_hdemo_sk bigint,\n"
                        + "    cs_ship_addr_sk bigint,\n"
                        + "    cs_call_center_sk bigint,\n"
                        + "    cs_catalog_page_sk bigint,\n"
                        + "    cs_ship_mode_sk bigint,\n"
                        + "    cs_warehouse_sk bigint,\n"
                        + "    cs_item_sk bigint,\n"
                        + "    cs_promo_sk bigint,\n"
                        + "    cs_order_number bigint,\n"
                        + "    cs_quantity integer,\n"
                        + "    cs_wholesale_cost decimal(7,2),\n"
                        + "    cs_list_price decimal(7,2),\n"
                        + "    cs_sales_price decimal(7,2),\n"
                        + "    cs_ext_discount_amt decimal(7,2),\n"
                        + "    cs_ext_sales_price decimal(7,2),\n"
                        + "    cs_ext_wholesale_cost decimal(7,2),\n"
                        + "    cs_ext_list_price decimal(7,2),\n"
                        + "    cs_ext_tax decimal(7,2),\n"
                        + "    cs_coupon_amt decimal(7,2),\n"
                        + "    cs_ext_ship_cost decimal(7,2),\n"
                        + "    cs_net_paid decimal(7,2),\n"
                        + "    cs_net_paid_inc_tax decimal(7,2),\n"
                        + "    cs_net_paid_inc_ship decimal(7,2),\n"
                        + "    cs_net_paid_inc_ship_tax decimal(7,2),\n"
                        + "    cs_net_profit decimal(7,2)\n"
                        + ")\n"
                        + "DUPLICATE KEY(cs_sold_date_sk, cs_sold_time_sk, cs_ship_date_sk, cs_bill_customer_sk)\n"
                        + "DISTRIBUTED BY HASH(cs_bill_customer_sk) BUCKETS 3\n"
                        + "PROPERTIES (\n"
                        + "  \"replication_num\" = \"1\"\n"
                        + ")\n"
                        + "\n",
                "CREATE TABLE IF NOT EXISTS customer_ds (\n"
                        + "    c_customer_sk bigint,\n"
                        + "    c_customer_id char(16),\n"
                        + "    c_current_cdemo_sk bigint,\n"
                        + "    c_current_hdemo_sk bigint,\n"
                        + "    c_current_addr_sk bigint,\n"
                        + "    c_first_shipto_date_sk bigint,\n"
                        + "    c_first_sales_date_sk bigint,\n"
                        + "    c_salutation char(10),\n"
                        + "    c_first_name char(20),\n"
                        + "    c_last_name char(30),\n"
                        + "    c_preferred_cust_flag char(1),\n"
                        + "    c_birth_day integer,\n"
                        + "    c_birth_month integer,\n"
                        + "    c_birth_year integer,\n"
                        + "    c_birth_country varchar(20),\n"
                        + "    c_login char(13),\n"
                        + "    c_email_address char(50),\n"
                        + "    c_last_review_date_sk bigint\n"
                        + ")\n"
                        + "DUPLICATE KEY(c_customer_sk, c_customer_id)\n"
                        + "DISTRIBUTED BY HASH(c_customer_id) BUCKETS 3\n"
                        + "PROPERTIES (\n"
                        + "  \"replication_num\" = \"1\"\n"
                        + ")\n"
                        + "\n",
                "CREATE TABLE IF NOT EXISTS customer_address (\n"
                        + "    ca_address_sk bigint,\n"
                        + "    ca_address_id char(16),\n"
                        + "    ca_street_number char(10),\n"
                        + "    ca_street_name varchar(60),\n"
                        + "    ca_street_type char(15),\n"
                        + "    ca_suite_number char(10),\n"
                        + "    ca_city varchar(60),\n"
                        + "    ca_county varchar(30),\n"
                        + "    ca_state char(2),\n"
                        + "    ca_zip char(10),\n"
                        + "    ca_country varchar(20),\n"
                        + "    ca_gmt_offset decimal(5,2),\n"
                        + "    ca_location_type char(20)\n"
                        + ")\n"
                        + "DUPLICATE KEY(ca_address_sk, ca_address_id)\n"
                        + "DISTRIBUTED BY HASH(ca_address_sk) BUCKETS 3\n"
                        + "PROPERTIES (\n"
                        + "  \"replication_num\" = \"1\"\n"
                        + ")\n"
                        + "\n",
                "CREATE TABLE IF NOT EXISTS customer_demographics (\n"
                        + "    cd_demo_sk bigint,\n"
                        + "    cd_gender char(1),\n"
                        + "    cd_marital_status char(1),\n"
                        + "    cd_education_status char(20),\n"
                        + "    cd_purchase_estimate integer,\n"
                        + "    cd_credit_rating char(10),\n"
                        + "    cd_dep_count integer,\n"
                        + "    cd_dep_employed_count integer,\n"
                        + "    cd_dep_college_count integer\n"
                        + ")\n"
                        + "DUPLICATE KEY(cd_demo_sk, cd_gender)\n"
                        + "DISTRIBUTED BY HASH(cd_gender) BUCKETS 3\n"
                        + "PROPERTIES (\n"
                        + "  \"replication_num\" = \"1\"\n"
                        + ")\n",
                "CREATE TABLE IF NOT EXISTS date_dim (\n"
                        + "    d_date_sk bigint,\n"
                        + "    d_date_id char(16),\n"
                        + "    d_date date,\n"
                        + "    d_month_seq integer,\n"
                        + "    d_week_seq integer,\n"
                        + "    d_quarter_seq integer,\n"
                        + "    d_year integer,\n"
                        + "    d_dow integer,\n"
                        + "    d_moy integer,\n"
                        + "    d_dom integer,\n"
                        + "    d_qoy integer,\n"
                        + "    d_fy_year integer,\n"
                        + "    d_fy_quarter_seq integer,\n"
                        + "    d_fy_week_seq integer,\n"
                        + "    d_day_name char(9),\n"
                        + "    d_quarter_name char(6),\n"
                        + "    d_holiday char(1),\n"
                        + "    d_weekend char(1),\n"
                        + "    d_following_holiday char(1),\n"
                        + "    d_first_dom integer,\n"
                        + "    d_last_dom integer,\n"
                        + "    d_same_day_ly integer,\n"
                        + "    d_same_day_lq integer,\n"
                        + "    d_current_day char(1),\n"
                        + "    d_current_week char(1),\n"
                        + "    d_current_month char(1),\n"
                        + "    d_current_quarter char(1),\n"
                        + "    d_current_year char(1)\n"
                        + ")\n"
                        + "DUPLICATE KEY(d_date_sk, d_date_id)\n"
                        + "DISTRIBUTED BY HASH(d_date_id) BUCKETS 3\n"
                        + "PROPERTIES (\n"
                        + "  \"replication_num\" = \"1\"\n"
                        + ")\n",
                "CREATE TABLE IF NOT EXISTS household_demographics (\n"
                        + "    hd_demo_sk bigint,\n"
                        + "    hd_income_band_sk bigint,\n"
                        + "    hd_buy_potential char(15),\n"
                        + "    hd_dep_count integer,\n"
                        + "    hd_vehicle_count integer\n"
                        + ")\n"
                        + "DUPLICATE KEY(hd_demo_sk, hd_income_band_sk)\n"
                        + "DISTRIBUTED BY HASH(hd_demo_sk) BUCKETS 3\n"
                        + "PROPERTIES (\n"
                        + "  \"replication_num\" = \"1\"\n"
                        + ")\n"
                        + "\n",
                "CREATE TABLE IF NOT EXISTS income_band (\n"
                        + "    ib_income_band_sk bigint,\n"
                        + "    ib_lower_bound integer,\n"
                        + "    ib_upper_bound integer\n"
                        + ")\n"
                        + "DUPLICATE KEY(ib_income_band_sk)\n"
                        + "DISTRIBUTED BY HASH(ib_income_band_sk) BUCKETS 3\n"
                        + "PROPERTIES (\n"
                        + "  \"replication_num\" = \"1\"\n"
                        + ")\n"
                        + "\n",
                "CREATE TABLE IF NOT EXISTS inventory (\n"
                        + "    inv_date_sk bigint,\n"
                        + "    inv_item_sk bigint,\n"
                        + "    inv_warehouse_sk bigint,\n"
                        + "    inv_quantity_on_hand integer\n"
                        + ")\n"
                        + "DUPLICATE KEY(inv_date_sk, inv_item_sk)\n"
                        + "DISTRIBUTED BY HASH(inv_warehouse_sk) BUCKETS 3\n"
                        + "PROPERTIES (\n"
                        + "  \"replication_num\" = \"1\"\n"
                        + ")\n"
                        + "\n",
                "CREATE TABLE IF NOT EXISTS item (\n"
                        + "    i_item_sk bigint,\n"
                        + "    i_item_id char(16),\n"
                        + "    i_rec_start_date date,\n"
                        + "    i_rec_end_date date,\n"
                        + "    i_item_desc varchar(200),\n"
                        + "    i_current_price decimal(7,2),\n"
                        + "    i_wholesale_cost decimal(7,2),\n"
                        + "    i_brand_id integer,\n"
                        + "    i_brand char(50),\n"
                        + "    i_class_id integer,\n"
                        + "    i_class char(50),\n"
                        + "    i_category_id integer,\n"
                        + "    i_category char(50),\n"
                        + "    i_manufact_id integer,\n"
                        + "    i_manufact char(50),\n"
                        + "    i_size char(20),\n"
                        + "    i_formulation char(20),\n"
                        + "    i_color char(20),\n"
                        + "    i_units char(10),\n"
                        + "    i_container char(10),\n"
                        + "    i_manager_id integer,\n"
                        + "    i_product_name char(50)\n"
                        + ")\n"
                        + "DUPLICATE KEY(i_item_sk, i_item_id)\n"
                        + "DISTRIBUTED BY HASH(i_item_sk) BUCKETS 3\n"
                        + "PROPERTIES (\n"
                        + "  \"replication_num\" = \"1\"\n"
                        + ")\n",
                "CREATE TABLE IF NOT EXISTS promotion (\n"
                        + "    p_promo_sk bigint,\n"
                        + "    p_promo_id char(16),\n"
                        + "    p_start_date_sk bigint,\n"
                        + "    p_end_date_sk bigint,\n"
                        + "    p_item_sk bigint,\n"
                        + "    p_cost decimal(15,2),\n"
                        + "    p_response_targe integer,\n"
                        + "    p_promo_name char(50),\n"
                        + "    p_channel_dmail char(1),\n"
                        + "    p_channel_email char(1),\n"
                        + "    p_channel_catalog char(1),\n"
                        + "    p_channel_tv char(1),\n"
                        + "    p_channel_radio char(1),\n"
                        + "    p_channel_press char(1),\n"
                        + "    p_channel_event char(1),\n"
                        + "    p_channel_demo char(1),\n"
                        + "    p_channel_details varchar(100),\n"
                        + "    p_purpose char(15),\n"
                        + "    p_discount_active char(1)\n"
                        + ")\n"
                        + "DUPLICATE KEY(p_promo_sk, p_promo_id)\n"
                        + "DISTRIBUTED BY HASH(p_promo_sk) BUCKETS 3\n"
                        + "PROPERTIES (\n"
                        + "  \"replication_num\" = \"1\"\n"
                        + ")\n"
                        + "\n",
                "CREATE TABLE IF NOT EXISTS reason (\n"
                        + "    r_reason_sk bigint,\n"
                        + "    r_reason_id char(16),\n"
                        + "    r_reason_desc char(100)\n"
                        + " )\n"
                        + "DUPLICATE KEY(r_reason_sk, r_reason_id)\n"
                        + "DISTRIBUTED BY HASH(r_reason_sk) BUCKETS 3\n"
                        + "PROPERTIES (\n"
                        + "  \"replication_num\" = \"1\"\n"
                        + ")\n"
                        + "\n",
                "CREATE TABLE IF NOT EXISTS ship_mode (\n"
                        + "    sm_ship_mode_sk bigint,\n"
                        + "    sm_ship_mode_id char(16),\n"
                        + "    sm_type char(30),\n"
                        + "    sm_code char(10),\n"
                        + "    sm_carrier char(20),\n"
                        + "    sm_contract char(20)\n"
                        + ")\n"
                        + "DUPLICATE KEY(sm_ship_mode_sk, sm_ship_mode_id)\n"
                        + "DISTRIBUTED BY HASH(sm_ship_mode_sk) BUCKETS 3\n"
                        + "PROPERTIES (\n"
                        + "  \"replication_num\" = \"1\"\n"
                        + ")\n"
                        + "\n",
                "CREATE TABLE IF NOT EXISTS store (\n"
                        + "    s_store_sk bigint,\n"
                        + "    s_store_id char(16),\n"
                        + "    s_rec_start_date date,\n"
                        + "    s_rec_end_date date,\n"
                        + "    s_closed_date_sk bigint,\n"
                        + "    s_store_name varchar(50),\n"
                        + "    s_number_employees integer,\n"
                        + "    s_floor_space integer,\n"
                        + "    s_hours char(20),\n"
                        + "    s_manager varchar(40),\n"
                        + "    s_market_id integer,\n"
                        + "    s_geography_class varchar(100),\n"
                        + "    s_market_desc varchar(100),\n"
                        + "    s_market_manager varchar(40),\n"
                        + "    s_division_id integer,\n"
                        + "    s_division_name varchar(50),\n"
                        + "    s_company_id integer,\n"
                        + "    s_company_name varchar(50),\n"
                        + "    s_street_number varchar(10),\n"
                        + "    s_street_name varchar(60),\n"
                        + "    s_street_type char(15),\n"
                        + "    s_suite_number char(10),\n"
                        + "    s_city varchar(60),\n"
                        + "    s_county varchar(30),\n"
                        + "    s_state char(2),\n"
                        + "    s_zip char(10),\n"
                        + "    s_country varchar(20),\n"
                        + "    s_gmt_offset decimal(5,2),\n"
                        + "    s_tax_percentage decimal(5,2)\n"
                        + ")\n"
                        + "DUPLICATE KEY(s_store_sk, s_store_id)\n"
                        + "DISTRIBUTED BY HASH(s_store_id) BUCKETS 3\n"
                        + "PROPERTIES (\n"
                        + "  \"replication_num\" = \"1\"\n"
                        + ")\n",
                "CREATE TABLE IF NOT EXISTS store_returns (\n"
                        + "    sr_returned_date_sk bigint,\n"
                        + "    sr_return_time_sk bigint,\n"
                        + "    sr_item_sk bigint,\n"
                        + "    sr_customer_sk bigint,\n"
                        + "    sr_cdemo_sk bigint,\n"
                        + "    sr_hdemo_sk bigint,\n"
                        + "    sr_addr_sk bigint,\n"
                        + "    sr_store_sk bigint,\n"
                        + "    sr_reason_sk bigint,\n"
                        + "    sr_ticket_number bigint,\n"
                        + "    sr_return_quantity integer,\n"
                        + "    sr_return_amt decimal(7,2),\n"
                        + "    sr_return_tax decimal(7,2),\n"
                        + "    sr_return_amt_inc_tax decimal(7,2),\n"
                        + "    sr_fee decimal(7,2),\n"
                        + "    sr_return_ship_cost decimal(7,2),\n"
                        + "    sr_refunded_cash decimal(7,2),\n"
                        + "    sr_reversed_charge decimal(7,2),\n"
                        + "    sr_store_credit decimal(7,2),\n"
                        + "    sr_net_loss decimal(7,2)\n"
                        + ")\n"
                        + "DUPLICATE KEY(sr_returned_date_sk, sr_return_time_sk, sr_item_sk)\n"
                        + "DISTRIBUTED BY HASH(sr_return_time_sk) BUCKETS 3\n"
                        + "PROPERTIES (\n"
                        + "  \"replication_num\" = \"1\"\n"
                        + ")\n"
                        + "\n",
                "CREATE TABLE IF NOT EXISTS store_sales (\n"
                        + "    ss_sold_date_sk bigint,\n"
                        + "    ss_sold_time_sk bigint,\n"
                        + "    ss_item_sk bigint,\n"
                        + "    ss_customer_sk bigint,\n"
                        + "    ss_cdemo_sk bigint,\n"
                        + "    ss_hdemo_sk bigint,\n"
                        + "    ss_addr_sk bigint,\n"
                        + "    ss_store_sk bigint,\n"
                        + "    ss_promo_sk bigint,\n"
                        + "    ss_ticket_number bigint,\n"
                        + "    ss_quantity integer,\n"
                        + "    ss_wholesale_cost decimal(7,2),\n"
                        + "    ss_list_price decimal(7,2),\n"
                        + "    ss_sales_price decimal(7,2),\n"
                        + "    ss_ext_discount_amt decimal(7,2),\n"
                        + "    ss_ext_sales_price decimal(7,2),\n"
                        + "    ss_ext_wholesale_cost decimal(7,2),\n"
                        + "    ss_ext_list_price decimal(7,2),\n"
                        + "    ss_ext_tax decimal(7,2),\n"
                        + "    ss_coupon_amt decimal(7,2),\n"
                        + "    ss_net_paid decimal(7,2),\n"
                        + "    ss_net_paid_inc_tax decimal(7,2),\n"
                        + "    ss_net_profit decimal(7,2)\n"
                        + ")\n"
                        + "DUPLICATE KEY(ss_sold_date_sk, ss_sold_time_sk, ss_item_sk, ss_customer_sk)\n"
                        + "DISTRIBUTED BY HASH(ss_customer_sk) BUCKETS 3\n"
                        + "PROPERTIES (\n"
                        + "  \"replication_num\" = \"1\"\n"
                        + ")\n"
                        + "\n"
                        + "\n",
                "CREATE TABLE IF NOT EXISTS time_dim (\n"
                        + "    t_time_sk bigint,\n"
                        + "    t_time_id char(16),\n"
                        + "    t_time integer,\n"
                        + "    t_hour integer,\n"
                        + "    t_minute integer,\n"
                        + "    t_second integer,\n"
                        + "    t_am_pm char(2),\n"
                        + "    t_shift char(20),\n"
                        + "    t_sub_shift char(20),\n"
                        + "    t_meal_time char(20)\n"
                        + ")\n"
                        + "DUPLICATE KEY(t_time_sk, t_time_id)\n"
                        + "DISTRIBUTED BY HASH(t_time_sk) BUCKETS 3\n"
                        + "PROPERTIES (\n"
                        + "  \"replication_num\" = \"1\"\n"
                        + ")\n",
                "CREATE TABLE IF NOT EXISTS warehouse (\n"
                        + "    w_warehouse_sk bigint,\n"
                        + "    w_warehouse_id char(16),\n"
                        + "    w_warehouse_name varchar(20),\n"
                        + "    w_warehouse_sq_ft integer,\n"
                        + "    w_street_number char(10),\n"
                        + "    w_street_name varchar(60),\n"
                        + "    w_street_type char(15),\n"
                        + "    w_suite_number char(10),\n"
                        + "    w_city varchar(60),\n"
                        + "    w_county varchar(30),\n"
                        + "    w_state char(2),\n"
                        + "    w_zip char(10),\n"
                        + "    w_country varchar(20),\n"
                        + "    w_gmt_offset decimal(5,2)\n"
                        + ")\n"
                        + "DUPLICATE KEY(w_warehouse_sk, w_warehouse_id)\n"
                        + "DISTRIBUTED BY HASH(w_warehouse_sk) BUCKETS 3\n"
                        + "PROPERTIES (\n"
                        + "  \"replication_num\" = \"1\"\n"
                        + ")\n"
                        + "\n",
                "CREATE TABLE IF NOT EXISTS web_page (\n"
                        + "        wp_web_page_sk bigint,\n"
                        + "        wp_web_page_id char(16),\n"
                        + "        wp_rec_start_date date,\n"
                        + "        wp_rec_end_date date,\n"
                        + "        wp_creation_date_sk bigint,\n"
                        + "        wp_access_date_sk bigint,\n"
                        + "        wp_autogen_flag char(1),\n"
                        + "        wp_customer_sk bigint,\n"
                        + "        wp_url varchar(100),\n"
                        + "        wp_type char(50),\n"
                        + "        wp_char_count integer,\n"
                        + "        wp_link_count integer,\n"
                        + "        wp_image_count integer,\n"
                        + "        wp_max_ad_count integer\n"
                        + ")\n"
                        + "DUPLICATE KEY(wp_web_page_sk, wp_web_page_id)\n"
                        + "DISTRIBUTED BY HASH(wp_web_page_sk) BUCKETS 3\n"
                        + "PROPERTIES (\n"
                        + "  \"replication_num\" = \"1\"\n"
                        + ")\n",
                "CREATE TABLE IF NOT EXISTS web_returns (\n"
                        + "    wr_returned_date_sk bigint,\n"
                        + "    wr_returned_time_sk bigint,\n"
                        + "    wr_item_sk bigint,\n"
                        + "    wr_refunded_customer_sk bigint,\n"
                        + "    wr_refunded_cdemo_sk bigint,\n"
                        + "    wr_refunded_hdemo_sk bigint,\n"
                        + "    wr_refunded_addr_sk bigint,\n"
                        + "    wr_returning_customer_sk bigint,\n"
                        + "    wr_returning_cdemo_sk bigint,\n"
                        + "    wr_returning_hdemo_sk bigint,\n"
                        + "    wr_returning_addr_sk bigint,\n"
                        + "    wr_web_page_sk bigint,\n"
                        + "    wr_reason_sk bigint,\n"
                        + "    wr_order_number bigint,\n"
                        + "    wr_return_quantity integer,\n"
                        + "    wr_return_amt decimal(7,2),\n"
                        + "    wr_return_tax decimal(7,2),\n"
                        + "    wr_return_amt_inc_tax decimal(7,2),\n"
                        + "    wr_fee decimal(7,2),\n"
                        + "    wr_return_ship_cost decimal(7,2),\n"
                        + "    wr_refunded_cash decimal(7,2),\n"
                        + "    wr_reversed_charge decimal(7,2),\n"
                        + "    wr_account_credit decimal(7,2),\n"
                        + "    wr_net_loss decimal(7,2)\n"
                        + ")\n"
                        + "DUPLICATE KEY(wr_returned_date_sk, wr_returned_time_sk, wr_item_sk, wr_refunded_customer_sk)\n"
                        + "DISTRIBUTED BY HASH(wr_refunded_customer_sk) BUCKETS 3\n"
                        + "PROPERTIES (\n"
                        + "  \"replication_num\" = \"1\"\n"
                        + ")\n",
                "CREATE TABLE IF NOT EXISTS web_sales (\n"
                        + "    ws_sold_date_sk bigint,\n"
                        + "    ws_sold_time_sk bigint,\n"
                        + "    ws_ship_date_sk bigint,\n"
                        + "    ws_item_sk bigint,\n"
                        + "    ws_bill_customer_sk bigint,\n"
                        + "    ws_bill_cdemo_sk bigint,\n"
                        + "    ws_bill_hdemo_sk bigint,\n"
                        + "    ws_bill_addr_sk bigint,\n"
                        + "    ws_ship_customer_sk bigint,\n"
                        + "    ws_ship_cdemo_sk bigint,\n"
                        + "    ws_ship_hdemo_sk bigint,\n"
                        + "    ws_ship_addr_sk bigint,\n"
                        + "    ws_web_page_sk bigint,\n"
                        + "    ws_web_site_sk bigint,\n"
                        + "    ws_ship_mode_sk bigint,\n"
                        + "    ws_warehouse_sk bigint,\n"
                        + "    ws_promo_sk bigint,\n"
                        + "    ws_order_number bigint,\n"
                        + "    ws_quantity integer,\n"
                        + "    ws_wholesale_cost decimal(7,2),\n"
                        + "    ws_list_price decimal(7,2),\n"
                        + "    ws_sales_price decimal(7,2),\n"
                        + "    ws_ext_discount_amt decimal(7,2),\n"
                        + "    ws_ext_sales_price decimal(7,2),\n"
                        + "    ws_ext_wholesale_cost decimal(7,2),\n"
                        + "    ws_ext_list_price decimal(7,2),\n"
                        + "    ws_ext_tax decimal(7,2),\n"
                        + "    ws_coupon_amt decimal(7,2),\n"
                        + "    ws_ext_ship_cost decimal(7,2),\n"
                        + "    ws_net_paid decimal(7,2),\n"
                        + "    ws_net_paid_inc_tax decimal(7,2),\n"
                        + "    ws_net_paid_inc_ship decimal(7,2),\n"
                        + "    ws_net_paid_inc_ship_tax decimal(7,2),\n"
                        + "    ws_net_profit decimal(7,2)\n"
                        + ")\n"
                        + "DUPLICATE KEY(ws_sold_date_sk, ws_sold_time_sk, ws_ship_date_sk, ws_item_sk)\n"
                        + "DISTRIBUTED BY HASH(ws_item_sk) BUCKETS 3\n"
                        + "PROPERTIES (\n"
                        + "  \"replication_num\" = \"1\"\n"
                        + ")\n"
                        + "\n",
                "CREATE TABLE IF NOT EXISTS web_site (\n"
                        + "    web_site_sk bigint,\n"
                        + "    web_site_id char(16),\n"
                        + "    web_rec_start_date date,\n"
                        + "    web_rec_end_date date,\n"
                        + "    web_name varchar(50),\n"
                        + "    web_open_date_sk bigint,\n"
                        + "    web_close_date_sk bigint,\n"
                        + "    web_class varchar(50),\n"
                        + "    web_manager varchar(40),\n"
                        + "    web_mkt_id integer,\n"
                        + "    web_mkt_class varchar(50),\n"
                        + "    web_mkt_desc varchar(100),\n"
                        + "    web_market_manager varchar(40),\n"
                        + "    web_company_id integer,\n"
                        + "    web_company_name char(50),\n"
                        + "    web_street_number char(10),\n"
                        + "    web_street_name varchar(60),\n"
                        + "    web_street_type char(15),\n"
                        + "    web_suite_number char(10),\n"
                        + "    web_city varchar(60),\n"
                        + "    web_county varchar(30),\n"
                        + "    web_state char(2),\n"
                        + "    web_zip char(10),\n"
                        + "    web_country varchar(20),\n"
                        + "    web_gmt_offset decimal(5,2),\n"
                        + "    web_tax_percentage decimal(5,2)\n"
                        + ")\n"
                        + "DUPLICATE KEY(web_site_sk, web_site_id)\n"
                        + "DISTRIBUTED BY HASH(web_site_sk) BUCKETS 3\n"
                        + "PROPERTIES (\n"
                        + "  \"replication_num\" = \"1\"\n"
                        + ")\n"
                        + "\n"
                        + "\n"
                        + "\n"
        );
    }

    @Override
    protected void runBeforeEach() throws Exception {
        StatementScopeIdGenerator.clear();
        connectContext.getSessionVariable().setDisableNereidsRules(getDisableNereidsRules());
    }

    protected String getDisableNereidsRules() {
        return "PRUNE_EMPTY_PARTITION";
    }

    protected LogicalCompatibilityContext constructContext(Plan p1, Plan p2, CascadesContext context) {
        StructInfo st1 = MaterializedViewUtils.extractStructInfo(p1, p1,
                context, new BitSet()).get(0);
        StructInfo st2 = MaterializedViewUtils.extractStructInfo(p2, p2,
                context, new BitSet()).get(0);
        RelationMapping rm = RelationMapping.generate(st1.getRelations(), st2.getRelations(), 8)
                .get(0);
        SlotMapping sm = SlotMapping.generate(rm);
        return LogicalCompatibilityContext.from(rm, sm, st1, st2);
    }
}
